﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using NanjingbeiRailwayStation.Core;
using NanjingbeiRailwayStation.Data.ClickHouse.Models;

namespace NanjingbeiRailwayStation.Data.ClickHouse.Services
{
    /// <summary>
    /// 
    /// </summary>
    public class RealTimeElectricalParametersService
    {

        /// <summary>
        /// 当前状态GetCurrentState
        /// </summary>
        /// <param name="imei"></param>
        /// <returns></returns>
        public async Task<Models.MeterCurrentState> GetCurrentState(string imei)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@" select ta.imei as  imei
 ,tb.InstallationLocation as InstallationLocation
 ,tb.InstallationPosition as InstallationPosition
 ,tb.DeviceMode as DeviceMode
 ,tb.DeviceClass as DeviceClass
 ,tb.DeviceName as DeviceName
 ,ta.eventTime as eventTime
 ,ta.Real_UA  as Real_UA
 ,ta.Real_UB as Real_UB
 ,ta.Real_UC as Real_UC
 ,ta.Real_IA as Real_IA
 ,ta.Real_IB as Real_IB
 ,ta.Real_IC as Real_IC
 ,ta.Real_TA as Real_TA
 ,ta.Real_TB as Real_TB
 ,ta.Real_TC as Real_TC
 ,ta.Real_TN as Real_TN
 ,ta.Real_LD as Real_LD
 ,ta.electric_fr as electric_fr
  ,ta.YGGL_P as YGGL_P
  ,ta.YGGL_PA as YGGL_PA
 ,ta.YGGL_PB as YGGL_PB
 ,ta.YGGL_PC as YGGL_PC
 ,ta.WGGL_Q as WGGL_Q
  ,ta.WGGL_QA as WGGL_QA
  ,ta.WGGL_QB as WGGL_QB
  ,ta.WGGL_QC as WGGL_QC
  ,ta.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL
 ,ta.ActiveEnergyImportInPhaseL1 as  ActiveEnergyImportInPhaseL1
 ,ta.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2
 ,ta.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3
 ,tday.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL_day
 ,tday.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1_day
 ,tday.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2_day
 ,tday.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3_day
 ,tmonth.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL_month
 ,tmonth.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1_month
 ,tmonth.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2_month
 ,tmonth.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3_month
  ,tyear.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL_year
 ,tyear.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1_year
 ,tyear.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2_year
 ,tyear.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3_year
 from jindujiangong.RealTimeElectricalParameters_Latest as ta final
 join jindujiangong.DeviceEntitie  as tb 
 on ta.imei=tb.imei
 join (
 --day
select imei,eventTime
,sumMerge(ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day
where toYYYYMMDD( eventTime)=toYYYYMMDD(now())
and imei ='{0}'
group by imei,eventTime 
) as tday
on ta.imei=tday.imei
join (
---month
select imei
,sum(ActiveEnergyImportInPhaseL) as  ActiveEnergyImportInPhaseL
,sum(ActiveEnergyImportInPhaseL1) as  ActiveEnergyImportInPhaseL1
,sum(ActiveEnergyImportInPhaseL2) as  ActiveEnergyImportInPhaseL2
,sum(ActiveEnergyImportInPhaseL3) as  ActiveEnergyImportInPhaseL3
from (
select imei,eventTime
,sumMerge(ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day
where toYYYYMM( eventTime)=toYYYYMM(now())
and imei ='{0}'
group by imei,eventTime 
) as tc
group by imei
) as tmonth
on ta.imei=tmonth.imei
join (
	---year
	select imei
	,sum(ActiveEnergyImportInPhaseL) as  ActiveEnergyImportInPhaseL
	,sum(ActiveEnergyImportInPhaseL1) as  ActiveEnergyImportInPhaseL1
	,sum(ActiveEnergyImportInPhaseL2) as  ActiveEnergyImportInPhaseL2
	,sum(ActiveEnergyImportInPhaseL3) as  ActiveEnergyImportInPhaseL3
	from (
		select imei,eventTime
		,sumMerge(ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
		,sumMerge(ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
		,sumMerge(ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
		,sumMerge(ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
		from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day
		where year(eventTime)=year(now())
		and imei ='{0}'
		group by imei,eventTime 
	) as tc
	group by imei
) as tyear
on ta.imei=tyear.imei
where ta.imei='{0}'", imei);

            var entitys = await db.Database.SqlQueryRaw<Models.MeterCurrentState>(sql).ToListAsync();
            return entitys.FirstOrDefault() ?? new MeterCurrentState();
        }


        /// <summary>
        /// 图表走势
        /// </summary>
        /// <param name="imei"></param>
        /// <returns></returns>
        public async Task<List<MeterChartTrend>> GetMeterChartTrend(string imei)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"SELECT imei, 
 Real_UA, Real_UB, Real_UC
 , Real_IA, Real_IB, Real_IC
 , Real_LD , eventTime
 , Real_TA, Real_TB, Real_TC, Real_TN
 , YGGL_P, YGGL_PA, YGGL_PB, YGGL_PC
 , WGGL_Q , WGGL_QA, WGGL_QB,WGGL_QC
 , ActiveEnergyImportInPhaseL, ActiveEnergyImportInPhaseL1, ActiveEnergyImportInPhaseL2, ActiveEnergyImportInPhaseL3
FROM jindujiangong.RealTimeElectricalParameters
where imei='{0}'
and (toYYYYMMDD(eventTime) = toYYYYMMDD(now()) or toYYYYMMDD(eventTime) = toYYYYMMDD(yesterday()))
order by eventTime", imei);

            var entitys = await db.Database.SqlQueryRaw<Models.MeterChartTrend>(sql).ToListAsync();
            return entitys;

        }


        /// <summary>
        /// 历史 用电量 （按天）
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="dateTimeBegin"></param>
        /// <param name="dateTimeEnd"></param>
        /// <returns></returns>
        public async Task<List<HistoricalElectricityChart>> GetHistoricalElectricityChart(string imei, DateTime dateTimeBegin, DateTime dateTimeEnd)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var startDayOfMonth = dateTimeBegin.ToString("yyyy-MM-dd HH:mm:ss");
            var endDayOfMonth = dateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");
            var sql = string.Format(@"select imei,toString(eventTime) as eventTimeStr
,sumMerge(ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day
where
imei ='{0}' and eventTime >=toDateTime('{1}') and eventTime <=toDateTime('{2}')
group by imei,eventTime
order by  imei,eventTime asc", imei, startDayOfMonth, endDayOfMonth);

            var entitys = await db.Database.SqlQueryRaw<Models.HistoricalElectricityChart>(sql).ToListAsync();
            return entitys;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="dateTimeBegin"></param>
        /// <param name="dateTimeEnd"></param>
        /// <returns></returns>
        public async Task<List<MeterChartTrend>> GetMeterHistroyChartTrend(string imei, DateTime dateTimeBegin, DateTime dateTimeEnd)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var startDayOfMonth = dateTimeBegin.ToString("yyyy-MM-dd HH:mm:ss");
            var endDayOfMonth = dateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");

            var sql = string.Format(@"SELECT imei, 
 Real_UA, Real_UB, Real_UC
 , Real_IA, Real_IB, Real_IC
 , Real_LD , eventTime
 , Real_TA, Real_TB, Real_TC, Real_TN
 , YGGL_P, YGGL_PA, YGGL_PB, YGGL_PC
 , WGGL_Q , WGGL_QA, WGGL_QB,WGGL_QC
 , ActiveEnergyImportInPhaseL, ActiveEnergyImportInPhaseL1, ActiveEnergyImportInPhaseL2, ActiveEnergyImportInPhaseL3
FROM jindujiangong.RealTimeElectricalParameters
where imei='{0}'
and eventTime>=toDateTime('{1}')  and eventTime<=toDateTime('{2}') 
order by eventTime", imei, startDayOfMonth,endDayOfMonth);

            var entitys = await db.Database.SqlQueryRaw<Models.MeterChartTrend>(sql).ToListAsync();
            return entitys;

        }

        /// <summary>
        /// 获取 每日用电量
        /// </summary>
        /// <returns></returns>
        public async Task< List<ElectricityConsumption>> GetElectricityConsumption(string imei,DateTime dateTimeBegin,DateTime  dateTimeEnd)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var startDayOfMonth = dateTimeBegin.ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = dateTimeEnd.ToString("yyyy-MM-dd 23:59:59");

            var sql = string.Format(@"select  
                            sum( (tNow.ActiveEnergyImportInPhaseL1_per_day-tYesterday.ActiveEnergyImportInPhaseL1_per_day)) as ActiveEnergyImportInPhaseL1
                          , sum( (tNow.ActiveEnergyImportInPhaseL2_per_day-tYesterday.ActiveEnergyImportInPhaseL2_per_day)) as ActiveEnergyImportInPhaseL2
                          , sum( (tNow.ActiveEnergyImportInPhaseL3_per_day-tYesterday.ActiveEnergyImportInPhaseL3_per_day)) as ActiveEnergyImportInPhaseL3
	                        from jindujiangong.RealTimeElectricalParameters_per_day as tNow
	                        join jindujiangong.RealTimeElectricalParameters_per_day as tYesterday
	                        on tNow.imei =tYesterday.imei
	                        and  tYesterday.eventTime_per_day= 
	                        toYYYYMMDD( date_sub(DAY,1,  toDate( concat( substr( toString( tNow.eventTime_per_day),1,4),
	                                  '-',
	                               substr( toString( tNow.eventTime_per_day),5,2),
	                                '-',
	                               substr( toString( tNow.eventTime_per_day),7,2),
	                               ' 00:00:00'
	                               ))))
	                        where imei='{0}'
	                       and tNow.eventTime_per_day>=toYYYYMMDD(toDate('{1}'))
                           and tNow.eventTime_per_day<=toYYYYMMDD(toDate('{2}'))
	                       group by tNow.eventTime_per_day", imei, startDayOfMonth, endDayOfMonth);


             var entitys = await db.Database.SqlQueryRaw<ElectricityConsumption>(sql).ToListAsync();

            return entitys;

          
        }
        /// <summary>
        /// 用电量按日统计(本月）
        /// </summary>
        /// <returns></returns>

        public async Task<List<DailyElectricityConsumptionStatistics>> GetDailyElectricityConsumptionStatistics() 
        {

            var now = DateTime.Now;


            var startDayOfMonth = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(now.Year, now.Month, now.Day).ToString("yyyy-MM-dd 23:59:59");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
                                    sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
                                    sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
                                    sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
                                    sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
                                    from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
                                    where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
                                    and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
                                    group by toString( toYYYYMMDD(eventTime)) as  eventTime
                                    order by eventTime ", startDayOfMonth, endDayOfMonth);


            var entitys = await db.Database.SqlQueryRaw<DailyElectricityConsumptionStatistics>(sql).ToListAsync();

            return entitys;



        }

        /// <summary>
        /// 用电量按日统计 (时间范围）
        /// </summary>
        /// <returns></returns>

        public async Task<List<DailyElectricityConsumptionStatistics>> GetDailyElectricityConsumptionStatisticsByDateRange(DateTime beginDate, DateTime endDate)
        {


            var startDayOfMonth = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(endDate.Year, endDate.Month, endDate.Day).ToString("yyyy-MM-dd 23:59:59");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
                                    sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
                                    sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
                                    sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
                                    sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
                                    from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
                                    where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
                                    and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
                                    group by toString( toYYYYMMDD(eventTime)) as  eventTime
                                    order by eventTime ", startDayOfMonth, endDayOfMonth);


            var entitys = await db.Database.SqlQueryRaw<DailyElectricityConsumptionStatistics>(sql).ToListAsync();

            return entitys;

           

        }


        /// <summary>
        /// <summary>
        /// 用电量按月统计(时间范围）
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public async Task<ResultRespose<List<MonthlyElectricityConsumptionStatistics>>> GetMonthlyElectricityConsumptionStatisticsByDateRange(DateTime beginDate, DateTime endDate)
        {


            var startDayOfMonth = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(endDate.Year, endDate.Month, endDate.Day) .ToString("yyyy-MM-dd 23:59:59");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
group by toString( toYYYYMM(eventTime)) as  eventTime", startDayOfMonth, endDayOfMonth);

            var entitys = await db.Database.SqlQueryRaw<MonthlyElectricityConsumptionStatistics>(sql).ToListAsync();

            return ResultRespose<List<MonthlyElectricityConsumptionStatistics>>.OKWithData(entitys);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<ResultRespose<List<MonthlyElectricityConsumptionStatistics>>> GetMonthlyElectricityConsumptionStatistics()
        {


       



            var now = DateTime.Now;


            var startDayOfMonth = new DateTime(now.Year, 1, 1).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(now.Year, now.Month, now.Day).ToString("yyyy-MM-dd 10:00:00");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
group by toString( toYYYYMM(eventTime)) as  eventTime", startDayOfMonth, endDayOfMonth);


            var entitys = await db.Database.SqlQueryRaw<MonthlyElectricityConsumptionStatistics>(sql).ToListAsync();


            return ResultRespose<List<MonthlyElectricityConsumptionStatistics>>.OKWithData(entitys);


        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>

        public async Task<ResultRespose<List<AnnualStatisticsOfElectricityConsumption>>> GetAnnualElectricityConsumptionStatisticsByDateRange(DateTime beginDate, DateTime endDate)
        {
           

            var startDayOfMonth = new DateTime(beginDate.Year, beginDate.Month, beginDate.Day).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(endDate.Year, endDate.Month, endDate.Day).ToString("yyyy-MM-dd 23:59:59");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
                                    sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
                                    sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
                                    sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
                                    sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
                                    from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
                                    where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
                                    and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
                                    group by toString(Year(eventTime)) as  eventTime
                                    order by eventTime ", startDayOfMonth, endDayOfMonth);


            var entitys = await db.Database.SqlQueryRaw<AnnualStatisticsOfElectricityConsumption>(sql).ToListAsync();


            return ResultRespose<List<AnnualStatisticsOfElectricityConsumption>>.OKWithData(entitys);
        }


        

        /// <summary>
        /// 用电量按年统计 
        /// </summary>
        /// <returns></returns>

        public async Task<ResultRespose<List<AnnualStatisticsOfElectricityConsumption>>> GetAnnualElectricityConsumptionStatistics()
        {

            var now = DateTime.Now;


            var startDayOfMonth = new DateTime(2025, 1, 1).ToString("yyyy-MM-dd 00:00:00");
            var endDayOfMonth = new DateTime(now.Year, now.Month, now.Day).ToString("yyyy-MM-dd 10:00:00");

            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"select eventTime,
                                    sumMerge(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
                                    sumMerge(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
                                    sumMerge(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
                                    sumMerge(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
                                    from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta
                                    where toYYYYMMDD(ta.eventTime)>=toYYYYMMDD(toDate('{0}'))
                                    and toYYYYMMDD(ta.eventTime)<=toYYYYMMDD(toDate('{1}'))
                                    group by toString(Year(eventTime)) as  eventTime
                                    order by eventTime ", startDayOfMonth, endDayOfMonth);


            var entitys = await db.Database.SqlQueryRaw<AnnualStatisticsOfElectricityConsumption>(sql).ToListAsync();


            return ResultRespose<List<AnnualStatisticsOfElectricityConsumption>>.OKWithData(entitys);


        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="minute"></param>
        /// <returns></returns>

        public async Task<List<DeviceLineCount>> GetDeviceLineCount(int minute = 60)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var sql = string.Format(@" select imei, count(*) as count from jindujiangong.RealTimeElectricalParameters_Latest
                    where  eventTime>(date_sub(now(), INTERVAL {0} minute)) 
                    group by imei", minute);

            var entity = await db.Database.SqlQueryRaw<DeviceLineCount>(sql).ToListAsync();

            return entity;

        }


        /// <summary>
        /// 是否在线
        /// </summary>
        /// <param name="imei"></param>
        /// <param name="minute"></param>
        /// <returns></returns>
        public async Task<bool> IsOnLine(string imei, int minute = 60)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();
            var sql = string.Format(@"select count(*) as count from jindujiangong.RealTimeElectricalParameters_Latest
                    where  eventTime>(date_sub(now(), INTERVAL {0} minute)) 
                    and imei ='{1}'", minute, imei);

            var entity = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entity.count > 0;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetTowerCranePowerMonitoringCount()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@" select count(ta.imei) as  count
 from jindujiangong.RealTimeElectricalParameters_Latest  as ta
 join jindujiangong.DeviceEntitie as tb
 on ta.imei=tb.imei 
 where  toYYYYMMDD( ta.eventTime)=toYYYYMMDD(now())");


            var entitys = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entitys.count;


        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<List<TowerCranePowerMonitoring>> GetTowerCranePowerMonitoringByPage(int pageIndex, int pageSize)
        {
            var m = pageIndex * pageSize;


            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"  select * from (
  select ta.imei as imei 
 ,ta.Real_UA as Real_UA
 ,ta.Real_UB  as Real_UB
 ,ta.Real_UC  as Real_UC
 ,ta.Real_IA as Real_IA
 ,ta.Real_IB  as Real_IB
 ,ta.Real_IC  as Real_IC
 ,tb.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL 
 ,tb.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1 
 ,tb.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2 
 ,tb.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3 
  ,tc.DeviceName as deviceName
 ,tc.InstallationLocation as installationLocation
 from jindujiangong.RealTimeElectricalParameters_Latest 
 as ta final
 join 
 (
select imei
,eventTime 
,sumMerge( ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge( ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
  ,sumMerge( ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
  ,sumMerge( ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as tb final
 where  toYYYYMMDD( tb.eventTime)=toYYYYMMDD(now())
 group by (imei,eventTime)
 ) as tb
 on ta.imei=tb.imei 
 join jindujiangong.DeviceEntitie as tc
 on ta.imei=tc.imei 
 where  toYYYYMMDD( ta.eventTime)=toYYYYMMDD(now()) 
 and  toYYYYMMDD( tb.eventTime)=toYYYYMMDD(now())
 ) limit {0},{1}",m,pageSize);


            var entitys = await db.Database.SqlQueryRaw<TowerCranePowerMonitoring>(sql).ToListAsync();



            return entitys;
        }

        /// <summary>
        /// 塔吊用电监控   TowerCranePowerMonitoring
        /// </summary>
        /// <returns></returns>

        public async Task<List<TowerCranePowerMonitoring>> GetTowerCranePowerMonitoring()
        {

        
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = string.Format(@"                        
  select ta.imei as imei 
 ,ta.Real_UA as Real_UA
 ,ta.Real_UB  as Real_UB
 ,ta.Real_UC  as Real_UC
 ,ta.Real_IA as Real_IA
 ,ta.Real_IB  as Real_IB
 ,ta.Real_IC  as Real_IC
 ,tb.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL 
 ,tb.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1 
 ,tb.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2 
 ,tb.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3 
  ,tc.DeviceName as deviceName
 ,tc.InstallationLocation as installationLocation
 from jindujiangong.RealTimeElectricalParameters_Latest 
 as ta final
 join 
 (
select imei
,eventTime 
,sumMerge( ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge( ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
  ,sumMerge( ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
  ,sumMerge( ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as tb final
 where  toYYYYMMDD( tb.eventTime)=toYYYYMMDD(now())
 group by (imei,eventTime)
 ) as tb
 on ta.imei=tb.imei 
 
 join jindujiangong.DeviceEntitie as tc
 on ta.imei=tc.imei 

 where  toYYYYMMDD( ta.eventTime)=toYYYYMMDD(now()) 
 and  toYYYYMMDD( tb.eventTime)=toYYYYMMDD(now())");


            var entitys = await db.Database.SqlQueryRaw<TowerCranePowerMonitoring>(sql).ToListAsync();



            return entitys;
            //TODO:塔吊状态。

           
        }


        /// <summary>
        /// 今日用电量(kWh)
        /// </summary>
        /// <returns></returns>
        /// 
       
        public async Task<TodayElectricityConsumption?> GetTodayElectricityConsumption()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"  select 
                                    sum(ActiveEnergyImportInPhaseL ) as ActiveEnergyImportInPhaseL,
                                    sum(ActiveEnergyImportInPhaseL1 ) as ActiveEnergyImportInPhaseL1,
                                    sum(ActiveEnergyImportInPhaseL2 ) as ActiveEnergyImportInPhaseL2,
                                    sum(ActiveEnergyImportInPhaseL3 ) as ActiveEnergyImportInPhaseL3
                                    from jindujiangong.RealTimeElectricalParameters_Settlement_By_Frequency as ta
                                    where toYYYYMMDD(ta.eventTime)=toYYYYMMDD(now()) and ta.eventTime>date_sub(now(),INTERVAL 24 HOUR)
                                    and ta.ActiveEnergyImportInPhaseL1>0";


            var entity = await db.Database.SqlQueryRaw<TodayElectricityConsumption>(sql).FirstOrDefaultAsync();

            return entity;
           

        }


        /// <summary>
        /// 塔吊 正常的设备数
        /// </summary>
        /// <returns></returns>
        public async Task<long> GetTowerCraneNormalCount()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select  count(*) as Count  from 
                            (
	                            select imei,max(eventTime) as maxRealTime
	                            from jindujiangong.RealTimeElectricalParameters
	                            where eventTime>date_sub(now(), INTERVAL 1 HOUR)
	                            group by imei 
                            ) as ta
                            left join 
                            (

                            select imei,max(eventTime)  maxEventTime  from jindujiangong.EventReport 
                            where  endsWith(eventName,'Alarm')
                            and eventTime>date_sub(now(), INTERVAL 1 HOUR)
                            group by imei
                            ) as tb
                            on ta.imei=tb.imei
                            where (date_add(now(), INTERVAL 0 HOUR)-INTERVAL 10 minute)>tb.maxEventTime";

            var entity = await db.Database.SqlQueryRaw<TowerCraneNormalCount>(sql).FirstOrDefaultAsync();

            return entity == null ? 0 : entity.Count;

        }

        /// <summary>
        /// 损耗比
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetLossRatioCount()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @" SELECT
        count(*) as count
        from jindujiangong.RealTimeElectricalParameters_Latest   as ta final
         join DeviceEntitie  as tb final
           on ta.imei =tb.imei
        where toYYYYMM(eventTime)=toYYYYMM(now())";


            var entitys = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entitys.count;


        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<List<TodayLossRatio>> GetLossRatioByPage(int pageIndex,int pageSize)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var m = pageIndex * pageSize;

            var sql = string.Format( @"        SELECT
          ta.imei
          ,ta.Real_LD 
          ,tb.DeviceName as deviceName
          ,tb.InstallationLocation as installationLocation
        from jindujiangong.RealTimeElectricalParameters_Latest   as ta final
         join DeviceEntitie  as tb final
           on ta.imei =tb.imei
        where toYYYYMM(eventTime)=toYYYYMM(now())
        order by Real_LD desc
        limit {0},{1}",m,pageSize);


            var entitys = await db.Database.SqlQueryRaw<TodayLossRatio>(sql).ToListAsync();

            return entitys;
        }


        
        /// <summary>
        /// 损耗比
        /// </summary>
        /// <returns></returns>


        public async Task< List<TodayLossRatio>> GetTodayLossRatio()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"        SELECT
          ta.imei
          ,ta.Real_LD 
          ,tb.DeviceName as deviceName
          ,tb.InstallationLocation as installationLocation
        from jindujiangong.RealTimeElectricalParameters_Latest   as ta final
         join DeviceEntitie  as tb final
           on ta.imei =tb.imei
        where toYYYYMM(eventTime)=toYYYYMM(now())
        order by Real_LD desc";


            var entitys = await db.Database.SqlQueryRaw<TodayLossRatio>(sql).ToListAsync();

            return entitys;




        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<ulong> GetEnergyConsumptionRankingCount()
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select 
count( tbb.imei) as count
from  jindujiangong.DeviceEntitie as tbb
 join 
(
select 
ta.imei as imei
,sumMerge(ta.ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ta.ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ta.ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ta.ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta final 
where  toYYYYMMDD( eventTime) =toYYYYMMDD( now()) 
group by imei,eventTime
) as taa final
on tbb.imei=taa.imei";


            var entitys = await db.Database.SqlQueryRaw<QueryCount>(sql).FirstAsync();

            return entitys.count;

        }



        /// <summary>
        /// 能耗排名
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>

        public async Task<List<EnergyConsumptionRanking>> GetEnergyConsumptionRankingByPage(int pageIndex,int pageSize)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var m = pageIndex * pageSize;

            var sql =string.Format( @"  select * from (
select 
tbb.imei
,tbb.DeviceName as deviceName
,tbb.InstallationLocation as installationLocation
,taa.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL
,taa.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1
,taa.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2
,taa.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3
from  jindujiangong.DeviceEntitie as tbb
 join 
(
select 
ta.imei as imei
,sumMerge(ta.ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ta.ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ta.ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ta.ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta final 
where  toYYYYMMDD( eventTime) =toYYYYMMDD( now()) 
group by imei,eventTime
) as taa final
on tbb.imei=taa.imei
order by taa.ActiveEnergyImportInPhaseL1 desc) limit {0},{1}",m,pageSize);
;
             
            var entitys = await db.Database.SqlQueryRaw<EnergyConsumptionRanking>(sql).ToListAsync();

            return entitys;
        }

        /// <summary>
        /// 能耗排名
        /// </summary>
        /// <returns></returns>


        public async Task<List<EnergyConsumptionRanking>> GetEnergyConsumptionRanking(int limit)
        {
            using var db = new NanjingbeiRailwayStation.Data.ClickHouse.Db();

            var sql = @"select 
tbb.imei
,tbb.DeviceName as deviceName
,tbb.InstallationLocation as installationLocation
,taa.ActiveEnergyImportInPhaseL as ActiveEnergyImportInPhaseL
,taa.ActiveEnergyImportInPhaseL1 as ActiveEnergyImportInPhaseL1
,taa.ActiveEnergyImportInPhaseL2 as ActiveEnergyImportInPhaseL2
,taa.ActiveEnergyImportInPhaseL3 as ActiveEnergyImportInPhaseL3
from  jindujiangong.DeviceEntitie as tbb
 join 
(
select 
ta.imei as imei
,sumMerge(ta.ActiveEnergyImportInPhaseL) as ActiveEnergyImportInPhaseL
,sumMerge(ta.ActiveEnergyImportInPhaseL1) as ActiveEnergyImportInPhaseL1
,sumMerge(ta.ActiveEnergyImportInPhaseL2) as ActiveEnergyImportInPhaseL2
,sumMerge(ta.ActiveEnergyImportInPhaseL3) as ActiveEnergyImportInPhaseL3
from jindujiangong.RealTimeElectricalParameters_Settlement_By_Day as ta final 
where  toYYYYMMDD( eventTime) =toYYYYMMDD( now()) 
group by imei,eventTime
) as taa final
on tbb.imei=taa.imei
order by taa.ActiveEnergyImportInPhaseL1 desc";


            var entitys = await db.Database.SqlQueryRaw<EnergyConsumptionRanking>(sql).ToListAsync();

            return entitys;
             

        }




    }
}
